'use strict';

const Controller = require('egg').Controller;

class VisitorController extends Controller {
  async index() {
    this.ctx.body = 'hi, egg';
  }
  async getVisitor() {
    var count = await this.ctx.app.db.all('select count (*) as count from visitor')
    let sql = `SELECT o.*,s.name as schoolName 
    FROM visitor as o inner join school as s on o.number=s.number  where 1=1 `
    let query = this.ctx.query
    let index = query.pageSize * (query.pageIndex - 1)
    if (query.startDate && query.endDate) {
      sql += ` and o.time>= '${query.startDate}' and o.time<= '${query.endDate}' `
      count = await this.ctx.app.db.all(`select count (*) as count from visitor where time>= '${query.startDate}' and time<= '${query.endDate}'`)
    }
    if (query.pageSize && query.pageIndex) {
      sql += ` order by o.time DESC limit ${query.pageSize} offset ${index} `
    }
    console.log(sql, 'yuju')
    var result = await this.ctx.app.db.all(sql)
    let res = {
      data: result,
      count
    }
    this.ctx.body = res
  }

  async editorVisitor() {
    const { db } = this.ctx.app;
    const peoples = this.ctx.request.body;
    let uID = peoples.uID;
    let people = {}
    for (var key in peoples) {
      people['$' + key] = peoples[key]
    }
    people.$id = uID
    delete people.$uID
    delete people.$time
    delete people.$schoolName
    let sqlResult = await db.all(`select * from people where id='${people.$id}'`)
    let data = {}
    if (sqlResult.length > 0) {
      data.message = '用户已存在，不能重复设置';
      data.result = false;
      this.ctx.body = data
    } else {
      let res = await db.run(`insert into people (id,vid,name,number,headImage,type,nation,startDate,endDate,issue,address,sex,shootphoto,birthday)
      values ($id,$vid,$name,$number,$headImage,$type,$nation,$startDate,$endDate,$issue,$address,$sex,$shootphoto,$birthday)`, people);
      if (res) {
        data.message = '操作成功';
        data.result = true;
      } else {
        data.message = '设置失败，请稍后再试试';
        data.result = false;
      }
      this.ctx.body = data
    }
  }

  async setVisitor() {
    const { db } = this.ctx.app;
    const visitors = this.ctx.request.body
    let visitor = {}
    for (var key in visitors) {
      visitor['$' + key] = visitors[key]
    }
    let res = await db.run(`insert into visitor (uID,vid,number,name,headImage,type,nation,startDate,endDate,issue,address,sex,shootphoto,birthday,time)
      values ($uID,$vid,$number,$name,$headImage,$type,$nation,$startDate,$endDate,$issue,$address,$sex,$shootphoto,$birthday,$time)`, visitor);
    this.ctx.body = res
  }
  async delVisitor() {
    const { db } = this.ctx.app;
    const id = this.ctx.request.query.id;
    const res = await db.run(`delete from visitor where id = ${id}`);
    this.ctx.body = res
  }
}

module.exports = VisitorController;
